<?php
namespace Administrator\Model;

use Zend\Db\Sql\Sql;
use Zend\Db\TableGateway\AbstractTableGateway;
use Zend\Db\Adapter\Adapter;
use Zend\Db\ResultSet\ResultSet;
use Zend\Db\TableGateway\TableGateway;
use Zend\Db\Sql\Expression;
use Zend\Crypt\PublicKey\Rsa\PublicKey;

class User extends AbstractTableGateway
{

    public $table = 'user';
    protected $tableGateway;
    protected $name;
    
    public function __construct(Adapter $adapter,TableGateway $tableGateway = null)
    {
        $this->adapter = $adapter;
        $this->resultSetPrototype = new ResultSet(ResultSet::TYPE_ARRAY);
        $this->initialize();
        $this->tableGateway = $tableGateway;
    }
    
    public function getUsers($where = array(), $columns = array())
    {
        try {
            $sql = new Sql($this->getAdapter());
            $select = $sql->select()->from(array(
                'user' => $this->table
            ));
            
            if (count($where) > 0) {
                $select->where($where);
            }
            
            if (count($columns) > 0) {
                $select->columns($columns);
            }
            
            $select->join(array('user_role' => 'user_role'), 'user_role.user_id = user.ID_USER', array('role_id'), 'LEFT');
            $select->join(array('role' => 'role'), 'user_role.role_id = role.rid', array('role_name'), 'LEFT');
            
            $statement = $sql->prepareStatementForSqlObject($select);
            $users = $this->resultSetPrototype->initialize($statement->execute())
                ->toArray();
            return $users;
        } catch (\Exception $e) {
            throw new \Exception($e->getPrevious()->getMessage());
        }
    }   

    public function countUser(){
    	return $this->tableGateway->select()->count();
    }
    
    public function listUser(){
		    try {
		            $sql = new Sql($this->getAdapter());
		            $select = $sql->select()->from(array(
		                'user' => $this->table
		            ));
		            
		            $select->join(array('user_role' => 'user_role'), 'user_role.user_id = user.ID_USER', array('role_id'), 'LEFT');
		            $select->join(array('role' => 'role'), 'user_role.role_id = role.rid', array('role_name'), 'LEFT');
		            
		            $statement = $sql->prepareStatementForSqlObject($select);
		            $users = $this->resultSetPrototype->initialize($statement->execute())
		                ->toArray();
		            return $users;
		        } catch (\Exception $e) {
		            throw new \Exception($e->getPrevious()->getMessage());
		        }
    }
    
 public function saveUser(ListUser $userdata,$id)
     {    	
         if($userdata->active_user == null)
         	$userdata->active_user = 0;
     	if ($id == 0) {
     		$data = array(
     				'EMAIL_USER' => $userdata->email_user ,
     				'PASS_USER' => md5('KHONGPHADUOCPASSDAUCUNG'.$userdata->password_user) ,
     				'ACTIVE' => $userdata->active_user ,
     				'NAME' => $userdata->name_user ,
     				'CREATED_ON' => date('Y-m-d H:i:s') ,
     		);
     		$this->tableGateway->insert($data);
     		 $select = $this->tableGateway->getSql()->select();
             $select->columns(array(
                   'ID_USER' => new Expression('MAX(ID_USER)')
	          ));
	         $resultSet = $this->tableGateway->selectWith ( $select );
	         $row = $resultSet->current()->ID_USER;
	         $dbAdapter = $this->adapter;
	         $sql       = 'INSERT INTO user_role (user_id,role_id)
                           VALUES ('.$row.','.$userdata->vaitro_user.');';
	         $statement = $dbAdapter->query($sql);
	         $result    = $statement->execute();
     	}else {
     		if($userdata->password_user_edit == null)
	     		$data = array(
	     				'EMAIL_USER' => $userdata->email_user ,
	     				'ACTIVE' => $userdata->active_user ,
	     				'NAME' => $userdata->name_user ,
	     				'MODIFIED_ON' => date('Y-m-d H:i:s'),
	     		);else 
	     			$data = array(
	     					'EMAIL_USER' => $userdata->email_user ,
	     					'PASS_USER' => md5('KHONGPHADUOCPASSDAUCUNG'.$userdata->password_user_edit) ,
	     					'ACTIVE' => $userdata->active_user ,
	     					'NAME' => $userdata->name_user ,
	     					'MODIFIED_ON' => date('Y-m-d H:i:s'),
	     			);
     		if ($this->getUser($id)) {
     			$this->tableGateway->update($data, array('ID_USER' => $id));
     			$dbAdapter = $this->adapter;
     			$sql  = 'UPDATE user_role SET role_id='.$userdata->vaitro_user.' WHERE user_id='.$id;
     			$statement = $dbAdapter->query($sql);
     			$result    = $statement->execute();
     		} else {
     			throw new \Exception('User id does not exist');
     		}
     	}
     } 

     public function updateActive($id,$idquery){
     	if($idquery == '1')
     		$idquery='0';
     	else
     		$idquery='1';
     	$data = array(
     			'ACTIVE' => $idquery,
     	);
     	$this->tableGateway->update($data, array('ID_USER' => $id));
     }
     
     public function getUser($id)
     {
     	$id  = (int) $id;
     	try {     		
     		$select = $this->tableGateway->getSql()->select();
     		$select->join(array('user_role' => 'user_role'), 'user_role.user_id = user.ID_USER', array('role_id'), 'inner');
     		$select->join(array('role' => 'role'), 'user_role.role_id = role.rid', array('role_name'), 'inner');
     		$select->where(array('user.ID_USER' => $id));
     		$resultSet = $this->tableGateway->selectWith ( $select );
     		$row = $resultSet->current();

     		$row->name_user = $row->NAME;
     		$row->email_user = $row->EMAIL_USER;
     		$row->active_user = $row->ACTIVE;
     		$row->vaitro_user = $row->role_id;
     		$row->password_user = $row->PASS_USER;

     		return $row;
     	} catch (\Exception $e) {
     		throw new \Exception($e->getPrevious()->getMessage());
     	}
     }
     
     public function deleteUser($id)
     {
     	$this->tableGateway->delete(array('ID_USER' => (int) $id));
     	$dbAdapter = $this->adapter;
     	$sql       = 'DELETE FROM user_role WHERE user_id='.$id;
     	$statement = $dbAdapter->query($sql);
     	$result    = $statement->execute();
     }
}
